<?php 
    namespace Activity\Repository;
    use Application\Repository\AbstractRepository;
    use Doctrine\ORM\Query\ResultSetMapping;
    
    class ActivityRepository extends AbstractRepository {
        protected  $entity="Activity\\Entity\\Activity";
        protected  $order=' order by c.id';
        
        
        public function listAll(){
            $query = $this->getEntityManager()->createQuery("SELECT c FROM $this->entity c".$this->order);
            return $query->getResult();
        }
        
        
        public function get($id){
            $query = $this->getEntityManager()->createQuery("SELECT c FROM $this->entity c where c.id = ?1".$this->order);
            $query->setParameter(1, $id);
            $query->setMaxResults(1);
            $results = $query->getResult();
            return (($results==null)||(count($results)==0))?null:$results[0];
        }
        
        protected $whereClausePublic = "";
        protected $whereClauseFriend = "  ((a.user_uid = :user_id) OR (a.user_uid in (SELECT f.user_to_uid FROM friend f WHERE f.user_from_uid = :user_id)))";
        protected $whereClauseFriendOfFriend = "  ((a.user_uid = :user_id) OR (a.user_uid in (SELECT f.user_to_uid FROM friend f WHERE f.user_from_uid = :user_id)) OR (a.user_uid in (SELECT f2.user_to_uid FROM friend f1, friend f2 WHERE f1.user_from_uid = :user_id AND f2.user_from_uid = f1.user_to_uid)))";
        /*protected $baseQuery = "SELECT a.id, a.created_at, a.modified_at, a.date, a.type as type
                , u.uid as uid, u.lastname as lastname, u.firstname as firstname, u.picwithlogo as picwithlogo
                , al.id as album_id, al.path as album_path, al.name as album_name
                , p.id as photo_id, p.path as photo_path, p.name as photo_name
                , v.id as video_id, v.url_video as url_video, v.url_thumbnail as url_thumbnail, v.name as video_name
                , c.id as comment_id, c.message as comment
                , m.id as mark_id, m.mark as mark_value, m.comment as mark_comment
                FROM activity a
                JOIN user u on a.user_uid= u.uid
                LEFT JOIN photo_activity pa on a.type='photo_activity' and a.id = pa.id
                LEFT JOIN photo p on p.id = pa.photo_id
                LEFT JOIN video_activity va on a.type='video_activity' and a.id = va.id
                LEFT JOIN video v on v.id = va.video_id
                LEFT JOIN album_activity al_a on a.type='album_activity' and a.id = al_a.id
                LEFT JOIN album al on al.id = al_a.album_id
                LEFT JOIN comment_activity ca on a.type='comment_activity' and a.id = ca.id
                LEFT JOIN comment c on c.id = ca.comment_id
                LEFT JOIN mark_activity ma on a.type='mark_activity' and a.id = ma.id
                LEFT JOIN mark m on m.id = ma.mark_id ";*/
        protected $baseQuery = "SELECT a.id, a.created_at, a.modified_at, a.date, a.type as type
                , u.uid as uid, u.lastname as lastname, u.firstname as firstname, u.picwithlogo as picwithlogo
                , al.id as album_id, al.path as album_path, al.name as album_name
                , p.id as photo_id, p.path as photo_path, p.name as photo_name
                , v.id as video_id, v.url_video as url_video, v.url_thumbnail as url_thumbnail, v.name as video_name
                , c.id as comment_id, c.message as comment
                , m.id as mark_id, m.mark as mark_value, m.comment as mark_comment, l.id as like_id
                FROM activity a
                JOIN user u on a.user_uid= u.uid
                LEFT JOIN photo_activity pa on a.type='photo_activity' and a.id = pa.id
                LEFT JOIN photo p on p.id = pa.photo_id
                LEFT JOIN video_activity va on a.type='video_activity' and a.id = va.id
                LEFT JOIN video v on v.id = va.video_id
                LEFT JOIN album_activity al_a on a.type='album_activity' and a.id = al_a.id
                LEFT JOIN album al on al.id = al_a.album_id
                LEFT JOIN comment_activity ca on a.type='comment_activity' and a.id = ca.id
                LEFT JOIN comment c on c.id = ca.comment_id
                LEFT JOIN mark_activity ma on a.type='mark_activity' and a.id = ma.id
                LEFT JOIN mark m on m.id = ma.mark_id
                LEFT JOIN like_activity la on a.type='like_activity' and a.id = la.id
                LEFT JOIN like_table l on l.id = la.like_id ";
        
        public function listActivities($adapter, $userId, $fromDate, $toDate, $filter = "public", $maxResult){
            $where = "";
            $bind = array("user_id"=>$userId);
            if($fromDate!=null) {
                $where = "WHERE a.date>=:from_date ";
                $bind["from_date"] = $fromDate;
            }
            if($toDate!=null) {
            	$where = (($where=="")?"WHERE ":"AND ")."a.date<=:to_date ";
            	$bind["to_date"] = $toDate;
            }
            $complWhere = "";//Filter on dates
            if($filter=="public") $complWhere = $this->whereClausePublic;
            else if($filter=="friend") $complWhere = $this->whereClauseFriend;
            else if($filter=="friend_of_friend") $complWhere = $this->whereClauseFriendOfFriend;
            if($complWhere!=""){
                if($where=="") $complWhere = "WHERE ".$complWhere;
                else $complWhere = "AND ".$complWhere;
            }
            $sql = $this->baseQuery.$where.$complWhere." ORDER BY a.date desc, a.id desc";
            
            if($maxResult!=null && $maxResult>0){
                $sql .= " LIMIT ".((int)$maxResult);
            }
            //var_dump($sql);
            return $adapter->query($sql, $bind);
        }
        
        
        
        public function listFromMax($adapter, $userId, $maxId, $filter, $maxResult=null, $excludeMax=true){
            $quantifier = $excludeMax?"<":"<=";
            
            $where = "";
            $bind = array();
            if($filter!="public") $bind["user_id"] = $userId;
            if($maxId!=null && $maxId>0) {
            	$where = "WHERE a.id ".$quantifier." :max_id ";
            	$bind["max_id"] = $maxId;
            }
            $complWhere = "";//Filter on dates
            if($filter=="public") $complWhere = $this->whereClausePublic;
            else if($filter=="friend") $complWhere = $this->whereClauseFriend;
            else if($filter=="friend_of_friend") $complWhere = $this->whereClauseFriendOfFriend;
            if($complWhere!=""){
            	if($where=="") $complWhere = "WHERE ".$complWhere;
            	else $complWhere = "AND ".$complWhere;
            }
            $sql = $this->baseQuery.
            $where.
            $complWhere.
            " ORDER BY a.date desc, a.id desc ";
            
            if($maxResult!=null && $maxResult>0){
                $sql .= " LIMIT ".((int)$maxResult);
            }
            
            //var_dump($sql);
            //var_dump($bind);
            return $adapter->query($sql, $bind);
        }
        public function listFromMin($adapter, $userId, $minId, $filter, $maxResult=null, $excludeMin=true){
            $quantifier = $excludeMin?">":">=";
            
            $where = "";
            $bind = array();
            if($filter!="public") $bind["user_id"] = $userId;
            if($minId!=null && $minId>0) {
                
            	$where = "WHERE a.id ".$quantifier." :min_id ";
            	$bind["min_id"] = $minId;
            }
            $complWhere = "";//Filter on dates
            if($filter=="public") $complWhere = $this->whereClausePublic;
            else if($filter=="friend") $complWhere = $this->whereClauseFriend;
            else if($filter=="friend_of_friend") $complWhere = $this->whereClauseFriendOfFriend;
            if($complWhere!=""){
            	if($where=="") $complWhere = "WHERE ".$complWhere;
            	else $complWhere = "AND ".$complWhere;
            }
            $sql = $this->baseQuery.
            $where.
            $complWhere.
            " ORDER BY a.date asc, a.id asc ";
            
            if($maxResult!=null && $maxResult>0){
                $sql .= " LIMIT ".((int)$maxResult);
            }

            //var_dump($sql);
            //var_dump($bind);
            return $adapter->query($sql, $bind);
        }
        
        
    }
